package com.xszx.dao.impl;

import com.xszx.beans.Goods;
import com.xszx.beans.User;
import com.xszx.dao.GoodsDao;
import com.xszx.util.JDBCUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class GoodsDaoImpl implements GoodsDao {
    @Override
    public void addGoods(Goods goods) {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "insert into t_goods values(null, ?, ?, ?, ?, ?, ?, ?, ?)";
            PreparedStatement ps = connection.prepareStatement(sql);

            ps.setString(1, goods.getGoodsName());
            ps.setString(2, goods.getGoodsUnit());
            ps.setDouble(3, goods.getGoodsPrice());
            ps.setString(4, goods.getGoodsImg());
            ps.setString(5, goods.getGoodsInfo());
            ps.setString(6, goods.getGoodsAddress());
            ps.setInt(7, goods.getNum());
            ps.setInt(8, goods.getState());

            ps.execute();

            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Goods> getAllGoods() {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select * from t_goods";
            PreparedStatement ps = connection.prepareStatement(sql);
            ResultSet resultSet = ps.executeQuery();

            List<Goods> list = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String goodsName = resultSet.getString("goodsName");
                String GoodsUnit = resultSet.getString("GoodsUnit");
                Double goodsPrice = resultSet.getDouble("goodsPrice");
                String GoodsImg = resultSet.getString("GoodsImg");
                String goodsInfo = resultSet.getString("goodsInfo");
                String goodsAddress = resultSet.getString("goodsAddress");
                Integer num = resultSet.getInt("num");
                Integer state = resultSet.getInt("state");

                Goods goods = new Goods(id, goodsName, GoodsUnit, goodsPrice, GoodsImg, goodsInfo, goodsAddress, num, state);
                list.add(goods);
            }


            connection.close();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void deleteGoods(List<Integer> ids) {
        Connection connection = null;
        try {
            connection = JDBCUtil.getConnection();
            String sql = "delete from t_goods where id in (?";

            for (int i = 1; i < ids.size(); i++) {
                sql += ", ?";
            }
            sql += ")";

            PreparedStatement ps = connection.prepareStatement(sql);

            for (int i = 0; i < ids.size(); i++) {
                ps.setInt(i+1, ids.get(i));
            }

            ps.execute();

            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public Goods getGoods(Integer id) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select * from t_goods where id = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, id);
            ResultSet resultSet = ps.executeQuery();

            while (resultSet.next()) {
                String goodsName = resultSet.getString("goodsName");
                String GoodsUnit = resultSet.getString("GoodsUnit");
                Double goodsPrice = resultSet.getDouble("goodsPrice");
                String GoodsImg = resultSet.getString("GoodsImg");
                String goodsInfo = resultSet.getString("goodsInfo");
                String goodsAddress = resultSet.getString("goodsAddress");
                Integer num = resultSet.getInt("num");
                Integer state = resultSet.getInt("state");

                Goods goods = new Goods(id, goodsName, GoodsUnit, goodsPrice, GoodsImg, goodsInfo, goodsAddress, num, state);
                return goods;
            }
            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public void updateGoods(Goods goods) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "update t_goods set goodsName = ?, goodsUnit = ?, goodsPrice = ?, goodsImg = ?, goodsInfo = ?, goodsAddress = ?, num = ?, state = ? where id = ?";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, goods.getGoodsName());
            ps.setString(2, goods.getGoodsUnit());
            ps.setDouble(3, goods.getGoodsPrice());
            ps.setString(4, goods.getGoodsImg());
            ps.setString(5, goods.getGoodsInfo());
            ps.setString(6, goods.getGoodsAddress());
            ps.setInt(7, goods.getNum());
            ps.setInt(8, goods.getState());
            ps.setInt(9, goods.getId());

            ps.execute();

            connection.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Goods> selectGoods(String name, Integer selectState) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select * from t_goods where goodsName like ?";
            if (selectState >= 0) {
                sql += " and state = ?";
            }
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setString(1, "%" + name + "%");
            if (selectState >= 0) {
                ps.setInt(2, selectState);
            }
            ResultSet resultSet = ps.executeQuery();

            List<Goods> list = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String goodsName = resultSet.getString("goodsName");
                String GoodsUnit = resultSet.getString("GoodsUnit");
                Double goodsPrice = resultSet.getDouble("goodsPrice");
                String GoodsImg = resultSet.getString("GoodsImg");
                String goodsInfo = resultSet.getString("goodsInfo");
                String goodsAddress = resultSet.getString("goodsAddress");
                Integer num = resultSet.getInt("num");
                Integer state = resultSet.getInt("state");

                Goods goods = new Goods(id, goodsName, GoodsUnit, goodsPrice, GoodsImg, goodsInfo, goodsAddress, num, state);
                list.add(goods);
            }

            connection.close();

            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    @Override
    public List<Goods> getGoodsIndex(Integer state) {
        try {
            Connection connection = JDBCUtil.getConnection();
            String sql = "select * from t_goods where state = ? limit 0, 4";
            PreparedStatement ps = connection.prepareStatement(sql);
            ps.setInt(1, state);
            ResultSet resultSet = ps.executeQuery();

            List<Goods> list = new ArrayList<>();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String goodsName = resultSet.getString("goodsName");
                String GoodsUnit = resultSet.getString("GoodsUnit");
                Double goodsPrice = resultSet.getDouble("goodsPrice");
                String GoodsImg = resultSet.getString("GoodsImg");
                String goodsInfo = resultSet.getString("goodsInfo");
                String goodsAddress = resultSet.getString("goodsAddress");
                Integer num = resultSet.getInt("num");

                Goods goods = new Goods(id, goodsName, GoodsUnit, goodsPrice, GoodsImg, goodsInfo, goodsAddress, num, state);
                list.add(goods);
            }

            connection.close();
            return list;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }
}
